﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Net;
using GenericTools;
using System.IO;
using GenericTools.IQServices;
//using DTS = Microsoft.SqlServer.Dts.Runtime;
using System.Data.Common;
using System.Data.SqlClient;
using DataModels.Enumerations;
using DataModels.Common;
using ReportingManagement.Components;

namespace ReportingManagement
{
    public partial class DownloadingData : Form
    {
        private WebClient webClient = null;
        private string destinationFile = null;
        private string downloadUrl = null;

        private DateTime startTime;
        private bool skipDownload = false;
        private bool success = false;
        private DateTime cutOffPoint;

        public const string OFFLINE_DATA_METADATA_TABLE = "ImportTables"; 
        private const int VIEW = 1;

        public DownloadingData(bool skipDownload, DateTime cutOffPoint)
        {
            if (AppConfiguration.AccessMethod == DataAccessMethod.DIRECT_ACCESS)
            {
                MessageBox.Show(this, "You can not perform this operation. The main datasource can not be deleted",
                    AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Stop);
                throw new Exception("You can not perform this operation. The main datasource can not be deleted");
            }

            InitializeComponent();
            webClient = new WebClient();
            webClient.DownloadProgressChanged += 
                new DownloadProgressChangedEventHandler(webClient_DownloadProgressChanged);
            webClient.DownloadFileCompleted += 
                new AsyncCompletedEventHandler(webClient_DownloadFileCompleted);
            
            this.skipDownload = skipDownload;
            this.cutOffPoint = cutOffPoint;
        }

        void webClient_DownloadProgressChanged(object sender, DownloadProgressChangedEventArgs e)
        {
            const int KILOBYTES = 1024;
            long KBReceived = e.BytesReceived / KILOBYTES;
            long KBTotal = e.TotalBytesToReceive / KILOBYTES;
            lblStatus.Text = String.Format("Downloading {0} KB of {1} KB", KBReceived, KBTotal);
            prbDownloading.Value = e.ProgressPercentage;
        }

        void webClient_DownloadFileCompleted(object sender, AsyncCompletedEventArgs e)
        {
            //downloadTimer.Stop();
            //lblTimeElapsed.Visible = false;
            Application.DoEvents();

            if (e.Error != null)
            {
                try
                {
                    WebException ex = (WebException)e.Error;
                    string errorText = String.Empty;
                    #region WebException Status Text
                    switch (ex.Status)
                    {
                        case WebExceptionStatus.NameResolutionFailure:
                            errorText = "The name resolver service could not resolve the host name.";
                            break;

                        case WebExceptionStatus.ConnectFailure:
                            errorText = "The remote service point could not be contacted at the transport level.";
                            break;

                        case WebExceptionStatus.ConnectionClosed:
                            errorText = "The connection was prematurely closed";
                            break;

                        case WebExceptionStatus.SendFailure:
                            errorText = "A complete request could not be sent to the remote server.";
                            break;

                        case WebExceptionStatus.MessageLengthLimitExceeded:
                            errorText = "A message was received that exceeded the specified limit " +
                                "when sending a request or receiving a response from the server.";
                            break;

                        case WebExceptionStatus.PipelineFailure:
                            errorText = "The request was a piplined request and the connection was "
                                + "closed before the response was received.";
                            break;

                        case WebExceptionStatus.ProtocolError:
                            errorText = "The response received from the server was complete but " +
                                "indicated a protocol-level error. For example, an HTTP protocol " +
                                "error such as 401 Access Denied would use this status.";
                            break;

                        case WebExceptionStatus.TrustFailure:
                            errorText = "A server certificate could not be validated.";
                            break;

                        case WebExceptionStatus.ServerProtocolViolation:
                            errorText = "The server response was not a valid HTTP response.";
                            break;

                        case WebExceptionStatus.KeepAliveFailure:
                            errorText = "The connection for a request that specifies the Keep-alive " +
                                "header was closed unexpectedly.";
                            break;

                        case WebExceptionStatus.Pending:
                            errorText = "An internal asynchronous request is pending.";
                            break;

                        case WebExceptionStatus.ReceiveFailure:
                            errorText = "A complete response was not received from the remote server.";
                            break;

                        case WebExceptionStatus.Success:
                            errorText = "No error was encountered.";
                            break;

                        case WebExceptionStatus.Timeout:
                            errorText = "No response was received during the time-out period for a request.";
                            break;

                        case WebExceptionStatus.ProxyNameResolutionFailure:
                            errorText = "The name resolver service could not resolve the proxy host name.";
                            break;

                        case WebExceptionStatus.RequestCanceled:
                            errorText = "The request was canceled, or an unclassifiable error occurred.";
                            break;

                        case WebExceptionStatus.RequestProhibitedByCachePolicy:
                            errorText = "The request was not permitted by the cache policy. In general, " +
                                "this occurs, when a request is not cacheable and the effective policy " +
                                "prohibits sending the request to the server. You might receive this status " +
                                "if a request method implies the presence of a request body, a request " +
                                "method requires direct interaction with the server, or a request contains a" +
                                "conditional header.";
                            break;

                        case WebExceptionStatus.RequestProhibitedByProxy:
                            errorText = "This request was not permitted by the proxy.";
                            break;

                        case WebExceptionStatus.UnknownError:
                        default:
                            errorText = "An exception of unknown type has occured.";
                            break;
                    }
                    #endregion
                    MessageBox.Show(this, errorText, "Error during downloading", 
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                    File.Delete(destinationFile);
                    this.success = false;
                    DialogResult = System.Windows.Forms.DialogResult.Cancel;
                    Close();
                }
                catch
                {
                    MessageBox.Show(this, e.Error.Message, "Error during downloading",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                    this.success = false;
                    DialogResult = System.Windows.Forms.DialogResult.Cancel;
                    File.Delete(destinationFile);
                    Close();
                }
                return;
            }

            if (!e.Cancelled)
            {
                prbDownloading.Value = prbDownloading.Maximum;
                lblStatus.Text = "Downloading is complete. Preparing to save data, please wait...";
                Cursor.Current = Cursors.WaitCursor;
                Application.DoEvents();
                string cacheFile = Properties.Settings.Default.CacheFileName;
                File.Delete(cacheFile);

                // Extracting the compressed file
                FileInfo compressedFile = new FileInfo(destinationFile);
                string originalFile = DataTransformation.Decompress(compressedFile);
                File.Delete(compressedFile.FullName);
                File.Move(originalFile, cacheFile);

                // Encrypting user passwords
                /*DbCommand command = GenericDataAccess.CreateCacheDBCommand();
                command.Connection.Open();
                command.CommandText = @"SELECT ID, Password FROM Users";
                DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
                foreach (DataRow row in dataTable.Rows)
                {
                    string id = row["ID"].ToString();
                    string password = row["Password"].ToString();
                    string encryptedPassword = DataTransformation.Encrypt(password);
                    command.CommandText = @"UPDATE Users SET [Password]=@Password WHERE ID=@ID";
                    command.Parameters.Clear();

                    DbParameter param = command.CreateParameter();
                    param.ParameterName = "@Password";
                    param.DbType = DbType.String;
                    param.Value = encryptedPassword;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@ID";
                    param.DbType = DbType.Int32;
                    param.Value = id;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();
                }
                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();*/
                
                if (Properties.Settings.Default.TypeOfCacheDB == CacheDB.SQL_SERVER)
                {
                    //LoadingDBIntoSQLServer();
                    btnCancel.Enabled = false;
                    backgroundWorker.RunWorkerAsync();
                    //LoadingDBIntoSQLServer(cacheFile);
                }
                if (!backgroundWorker.IsBusy)
                {
                    DialogResult = System.Windows.Forms.DialogResult.OK;
                    Close();
                }
            }
        }

        //public static bool LoadingDBIntoSQLServer()
        //{
        //    //AppConfiguration.DBConnectionString = AppConfiguration.SQLCacheDBConnection;
        //    DbCommand command = GenericDataAccess.CreateCommand(
        //        AppConfiguration.DBProviderName, AppConfiguration.SQLCacheDBConnection);
        //    try
        //    {
        //        #region Importing data using SQL Server Integration Services
        //        command.Connection.Open();
        //        command.CommandText = String.Format("DROP DATABASE {0}", AppConfiguration.CacheDBName);
        //        try { command.ExecuteNonQuery(); }
        //        catch { };
        //        command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
        //        command.ExecuteNonQuery();
        //        command.Connection.Close();

        //        DTS.Package dtsPackage = null;
        //        DTS.Application dtsApplication = null;

        //        dtsApplication = new DTS.Application();
        //        //Load package by specifying SSIS package file path
        //        string packageFilename = String.Format(@"{0}\Resources\{1}",
        //            Application.StartupPath, AppConfiguration.SSISPackageFilename);
        //        dtsPackage = dtsApplication.LoadPackage(packageFilename, null);
        //        DTS.Connections connections = dtsPackage.Connections;

        //        //DTS.ConnectionManager sourceConnection = connections["SourceConnectionOLEDB"];
        //        //sourceConnection.ConnectionString = AppConfiguration.CacheDBConnectionString;

        //        DTS.ConnectionManager destinationConnection = connections["DestinationConnectionOLEDB"];
        //        string connectionString = destinationConnection.ConnectionString;
        //        string[] components = connectionString.Split(';');
        //        foreach (string component in components)
        //        {
        //            if (component.StartsWith("Data Source="))
        //            {
        //                string cacheSQLConnectionString = AppConfiguration.SQLCacheDBConnection;
        //                string[] parts = cacheSQLConnectionString.Split(';');
        //                foreach (string part in parts)
        //                {
        //                    if (part.StartsWith("Data Source="))
        //                    {
        //                        connectionString = connectionString.Replace(component, part);
        //                        destinationConnection.ConnectionString = connectionString;
        //                        break;
        //                    }
        //                }
        //                break;
        //            }
        //        }
        //        //string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", "IQSMS_CacheDB");
        //        //destinationConnection.ConnectionString = cacheDBConnection;

        //        DTS.DTSExecResult packageResult = dtsPackage.Execute();
        //        bool success = false;
        //        switch (packageResult)
        //        {
        //            case DTS.DTSExecResult.Success:
        //                AppConfiguration.AccessMethod = DataAccessMethod.OFFLINE_DIRECT_ACCESS;
        //                string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
        //                AppConfiguration.DBConnectionString = cacheDBConnection;
        //                success = true;
        //                break;

        //            case DTS.DTSExecResult.Failure:
        //                DTS.DtsErrors errors = dtsPackage.Errors;
        //                foreach (DTS.DtsError error in errors)
        //                {
        //                    string errorText = String.Format(
        //                        "Error Code: {0}\nDescription: {1}\nSource: {2}\nSubComponent: {3}\nInterface: {4}",
        //                        error.ErrorCode, error.Description, error.Source,
        //                        error.SubComponent, error.IDOfInterfaceWithError);
        //                    MessageBox.Show(errorText, AppConfiguration.ApplicationName,
        //                        MessageBoxButtons.OK, MessageBoxIcon.Error);
        //                }
        //                success = false;
        //                break;

        //            case DTS.DTSExecResult.Completion:
        //                success = false;
        //                break;

        //            case DTS.DTSExecResult.Canceled:
        //                success = false;
        //                break;
        //        }
        //        return success;
        //        #endregion
        //    }
        //    catch (Exception ex)
        //    {
        //        string message = String.Format("{0}{1}{1}The application will try to import data again", 
        //            ex.Message, Environment.NewLine);
        //        MessageBox.Show(message, AppConfiguration.ApplicationName,
        //            MessageBoxButtons.OK, MessageBoxIcon.Error);
        //        try
        //        {
        //            #region Importing data using SQL Server bulk insert
        //            command = GenericDataAccess.CreateCommand(
        //                AppConfiguration.DBProviderName, AppConfiguration.SQLCacheDBConnection);
        //            command.Connection.Open();
        //            command.CommandText = String.Format("DROP DATABASE {0}", AppConfiguration.CacheDBName);
        //            try { command.ExecuteNonQuery(); }
        //            catch { };
        //            command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
        //            command.ExecuteNonQuery();
        //            command.Connection.Close();                    

        //            // Retrieving a list of table together with their data and insert into the database
        //            string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
        //            DbCommand cacheCommand = GenericDataAccess.CreateCacheDBCommand();
        //            cacheCommand.CommandText = @"SELECT [TableName] FROM [ImportTables]";
        //            DataTable tableList = GenericDataAccess.ExecuteSelectCommand(cacheCommand, false);
        //            foreach (DataRow row in tableList.Rows)
        //            {
        //                string tableName = row["TableName"].ToString();
        //                cacheCommand.CommandText = String.Format("SELECT * FROM [{0}]", tableName);
        //                DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(cacheCommand, false);
        //                CreateTable(cacheDBConnection, tableName, dataTable);
        //            }                    
        //            return true;
        //            #endregion
        //        }
        //        catch (Exception exc)
        //        {
        //            MessageBox.Show(exc.Message, AppConfiguration.ApplicationName,
        //                MessageBoxButtons.OK, MessageBoxIcon.Error);
        //        }
        //        return false;
        //    }
        //}

        private bool LoadingDBIntoSQLServer(string cacheFile)
        {
            //AppConfiguration.DBConnectionString = AppConfiguration.SQLCacheDBConnection;
            DbCommand command = GenericDataAccess.CreateCommand(
                AppConfiguration.DBProviderName, 
                AppConfiguration.SQLCacheDBConnection.Replace(AppConfiguration.CacheDBName, "master"));
            string reportingMessage = null; 
            try
            {
                #region Importing data using SQL Server bulk insert
                SqlConnection.ClearAllPools();  // Tries to clear existing connections.
                //command = GenericDataAccess.CreateCommand(AppConfiguration.DBProviderName, AppConfiguration.SQLCacheDBConnection);
                //command.Connection.Open();
                DbParameter param = null;
                int count = -1;

                #region Creating Target Database
                try
                {
                    string query = string.Format(
                        @"IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = '{0}' OR name = '{0}')))
                          BEGIN
                            ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                            DROP DATABASE [{0}]
                          END", 
                        AppConfiguration.CacheDBName);
                    command.Parameters.Clear();
                    command.CommandText = query;
                    GenericDataAccess.ExecuteNonQuery(command, false);
//                    command.CommandText = @"IF (EXISTS (SELECT name FROM master.dbo.sysdatabases 
//                        WHERE ('[' + name + ']' = @DatabaseName OR name = @DatabaseName))) DROP DATABASE [IQSMS_CacheDB]";
//                    param = command.CreateParameter();
//                    param.ParameterName = "@DatabaseName";
//                    param.DbType = DbType.String;
//                    param.Value = AppConfiguration.CacheDBName;
//                    command.Parameters.Add(param);
                    //string output = GenericDataAccess.ExecuteScalar(command, false);
                    //count = Int32.Parse(output);
                    //if (count != 1)
                    //{
                    //    command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
                    //    reportingMessage = "Creating new cache database. Please wait...";
                    //    backgroundWorker.ReportProgress(-1, reportingMessage);
                    //    command.ExecuteNonQuery();
                    //    command.Connection.Close();
                    //}
                    //else
                    //{
                    //    reportingMessage = "Cache database already existing. Preparing to recreate tables. Please wait...";
                    //    backgroundWorker.ReportProgress(-1, reportingMessage);
                    //}

                    command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
                    reportingMessage = "Creating new cache database. Please wait...";
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                    command.ExecuteNonQuery();
                    command.Connection.Close();
                }
                catch (Exception ex)
                {
                    reportingMessage = String.Format("Failed to create database: {0}", ex.Message);
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                    MessageBox.Show(ex.Message, 
                        String.Format("Failed to created database: {0}", AppConfiguration.CacheDBName), 
                        MessageBoxButtons.OK, MessageBoxIcon.Stop);
                    return false;
                };
                #endregion              

                // Retrieving a list of table together with their data and insert into the database
                string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
                command = GenericDataAccess.CreateCommand(AppConfiguration.DBProviderName, cacheDBConnection);
                command.CommandType = CommandType.Text;

                DataSet offlineDataSet = new DataSet();
                reportingMessage = "Reading data source. Please wait...";
                backgroundWorker.ReportProgress(-1, reportingMessage);
                offlineDataSet.ReadXml(cacheFile, XmlReadMode.ReadSchema);

                DataTable dataTable = offlineDataSet.Tables[OFFLINE_DATA_METADATA_TABLE];
                DataView dataView = dataTable.DefaultView; 
                dataView.Sort = "CreationOrder ASC";
                DataTable metadataTable = dataView.ToTable();

                #region Deleting Existing Target Tables
                int totalProgress = metadataTable.Rows.Count;
                count = 0;
                int progress;
                //// we delete in reverse order of creation, this resolves referencing
                //for (int i = metadataTable.Rows.Count - 1; i >= 0; i--)
                //{
                //    DataRow r = metadataTable.Rows[i];
                //    string tableName = r["TableName"].ToString();

                //    count++;
                //    decimal percentage = 100 * (decimal)count / totalProgress;
                //    progress = (int)percentage;
                //    reportingMessage = String.Format("Deleting old data table: {0}", tableName);
                //    backgroundWorker.ReportProgress(progress, reportingMessage);
                    
                //    command.CommandText = @"SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(@TableName) AND type in (N'U')";
                //    command.Parameters.Clear();
                //    param = command.CreateParameter();
                //    param.ParameterName = "@TableName";
                //    param.DbType = DbType.String;
                //    param.Value = tableName;
                //    command.Parameters.Add(param);
                //    string output = GenericDataAccess.ExecuteScalar(command, false);
                //    count = Int32.Parse(output);
                //    if (count == 1)
                //    {
                //        string deleteQuery = String.Format("DROP TABLE [{0}]", tableName);
                //        command.CommandText = deleteQuery;
                //        GenericDataAccess.ExecuteNonQuery(command, false);
                //    }
                //}
                #endregion

                #region Creating Target Tables and Importing Data
                foreach (DataRow r in metadataTable.Rows)
                {
                    string tableName = r["TableName"].ToString();
                    string createSQL = r["CreateSQL"].ToString();

                    if (tableName == OFFLINE_DATA_METADATA_TABLE) 
                    {
                        // Create table schema
                        string[] stringSeparator = new string[] { "GO" };
                        string[] queries = createSQL.Split(stringSeparator, StringSplitOptions.RemoveEmptyEntries);

                        totalProgress = queries.Length + metadataTable.Rows.Count;
                        foreach (string query in queries)
                        {
                            count++;
                            decimal percentage = 100 * (decimal)count / totalProgress;
                            progress = (int)percentage;
                            reportingMessage = String.Format("Creating database structure. Please wait...", tableName);
                            backgroundWorker.ReportProgress(progress, reportingMessage);

                            if (string.IsNullOrEmpty(query)) continue;
                            command.CommandText = query;
                            GenericDataAccess.ExecuteNonQuery(command, false);
                        }
                        break;
                    }
                }

                foreach (DataRow r in metadataTable.Rows)
                {
                    string tableName = r["TableName"].ToString();
                    string createSQL = r["CreateSQL"].ToString();
                    count++;

                    // This will stop populating data into metadata table
                    if (tableName == OFFLINE_DATA_METADATA_TABLE) continue;

                    decimal percentage = 100 * (decimal)count / totalProgress;
                    progress = (int)percentage;
                    reportingMessage = String.Format("Inserting data into new table: {0}. Please wait...", tableName);
                    backgroundWorker.ReportProgress(progress, reportingMessage);

                    // Create table schema
                    string[] stringSeparator = new string[] { "GO" };
                    string[] queries = createSQL.Split(stringSeparator, StringSplitOptions.RemoveEmptyEntries);                  
                    foreach (string query in queries)
                    {
                        if (string.IsNullOrEmpty(query)) continue;
                        command.CommandText = query;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }                    

                    DataTable dataSource = offlineDataSet.Tables[tableName];
                    bool imported = ImportTableData(command.Connection.ConnectionString, dataSource);
                    if (!imported) return false;
                }
                #endregion
                #endregion

                reportingMessage = "Cleaning up information. Please wait...";
                backgroundWorker.ReportProgress(-1, reportingMessage);
                #region Encrypting users' passwords and CacheDB labels
                /* We no longer need this encryption, data is already stored securely
                command.CommandText = @"SELECT ID, Password FROM Users";
                DataTable usersDataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
                foreach (DataRow row in usersDataTable.Rows)
                {
                    string id = row["ID"].ToString();
                    string password = row["Password"].ToString();
                    string encryptedPassword = DataTransformation.Encrypt(password);
                    command.CommandText = @"UPDATE Users SET [Password]=@Password WHERE ID=@ID";
                    command.Parameters.Clear();

                    param = command.CreateParameter();
                    param.ParameterName = "@Password";
                    param.DbType = DbType.String;
                    param.Value = encryptedPassword;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@ID";
                    param.DbType = DbType.Int32;
                    param.Value = id;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();
                }
                */

                command.CommandText = @"INSERT INTO SysSettings(SettingName,SettingValue) 
                    VALUES(@SettingName, @SettingValue)";
                // CacheDB update date
                param = command.CreateParameter();
                param.ParameterName = "@SettingName";
                param.DbType = DbType.String;
                param.Value = Utilities.SETTING_CACHEDB_CREATE_DATE;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@SettingValue";
                param.DbType = DbType.String;
                param.Value = DateTime.Now.ToString("s");
                command.Parameters.Add(param);
                command.ExecuteNonQuery();

                // Data cut-off point
                command.Parameters.Clear();
                param = command.CreateParameter();
                param.ParameterName = "@SettingName";
                param.DbType = DbType.String;
                param.Value = Utilities.SETTING_CACHEDB_CUT_OFF_POINT;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@SettingValue";
                param.DbType = DbType.String;
                param.Value = this.cutOffPoint.ToString("s");
                command.Parameters.Add(param);
                command.ExecuteNonQuery();
                #endregion

                #region Creating Tables and Views from Queries
                command.CommandText = "SELECT [Name],[InternalName],[Definition],[CreateTable],[QueryTypeId] FROM Queries";
                DataTable queriesDataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
                
                totalProgress += queriesDataTable.Rows.Count;
                foreach (DataRow row in queriesDataTable.Rows)
                {
                    int queryType = Int32.Parse(row["QueryTypeId"].ToString());
                    string viewName = row["InternalName"].ToString();

                    count++;
                    decimal percentage = 100 * (decimal)count / totalProgress;
                    progress = (int)percentage;
                    reportingMessage = String.Format("Creating and inserting data into views and associated tables: {0}. Please wait...", viewName);
                    backgroundWorker.ReportProgress(progress, reportingMessage);
                   
                    if (queryType == VIEW)
                    {
                        #region Creating a view form query definition
                        string viewDefinition = row["Definition"].ToString();
                        command.CommandText = @"SELECT COUNT(*) FROM sys.views WHERE name=@ViewName";
                        command.Parameters.Clear();
                        param = command.CreateParameter();
                        param.ParameterName = @"ViewName";
                        param.DbType = DbType.String;
                        param.Value = viewName;
                        command.Parameters.Add(param);
                        string output = GenericDataAccess.ExecuteScalar(command, false);

                        int c = Int32.Parse(output);
                        string query = null;
                        if (c == 1)
                        {
                            query = String.Format("DROP VIEW {0}", viewName);
                            command.CommandText = query;
                            command.Parameters.Clear();
                            GenericDataAccess.ExecuteNonQuery(command, false);
                        }
                        query = String.Format("CREATE VIEW {0} AS {1}", viewName, viewDefinition);
                        command.CommandText = query;
                        command.Parameters.Clear();
                        GenericDataAccess.ExecuteNonQuery(command, false);
                        #endregion

                        #region Creating a table from view if required
                        bool createTable = Boolean.Parse(row["CreateTable"].ToString());
                        if (createTable)
                        {
                            command.CommandText = @"SELECT COUNT(*) FROM sys.tables WHERE name=@TableName";
                            command.Parameters.Clear();
                            param = command.CreateParameter();
                            param.ParameterName = @"TableName";
                            param.DbType = DbType.String;
                            param.Value = String.Format("tbl_{0}", viewName);
                            command.Parameters.Add(param);
                            output = GenericDataAccess.ExecuteScalar(command, false);

                            int j = Int32.Parse(output);
                            if (j == 1)
                            {
                                command.CommandText = String.Format("DROP TABLE tbl_{0}", viewName);
                                GenericDataAccess.ExecuteNonQuery(command, false);
                            }

                            int offset = viewDefinition.IndexOf("FROM", 0, StringComparison.OrdinalIgnoreCase);
                            string p1 = viewDefinition.Substring(0, offset);
                            string p2 = viewDefinition.Substring(offset);
                            string tableName = String.Format("tbl_{0}", viewName);
                            string createQuery = String.Format("{0} INTO {1} {2}", p1, tableName, p2);
                                                        
                            try
                            {
                                command.CommandText = createQuery;
                                GenericDataAccess.ExecuteNonQuery(command, false);
                            }
                            catch
                            {
                                reportingMessage = String.Format("Failed to create and insert data into views and associated tables: {0}. Please wait...", viewName);
                                backgroundWorker.ReportProgress(progress, reportingMessage);
                            }
                        }
                        #endregion
                    }
                } 
                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                return true;
                #endregion
            }
            catch (Exception ex)
            {
                string message = String.Format("{0}{1}{1}The application failed to import data.",
                    ex.Message, Environment.NewLine);
                Utilities.LogError(ex, command);
                MessageBox.Show(message, AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Error);
                
                return false;
            }
        }

        public static bool LoadingDBIntoSQLServer(IWin32Window owner)
        {
            var cutOffPointEditor = new OfflineDataCutOffPoint();
            var helper = new System.Windows.Interop.WindowInteropHelper(cutOffPointEditor);
            helper.Owner = owner.Handle;
            bool? okToContinue = cutOffPointEditor.ShowDialog();
            if (okToContinue.HasValue && okToContinue.Value)
            {
                DateTime cutOffPoint = cutOffPointEditor.CutOffPoint;
                DownloadingData downloadingData = new DownloadingData(true, cutOffPoint);
                downloadingData.ShowDialog(owner);
                return downloadingData.Completed;
            }
            else return false;
        }

        private static string GetDBDataType(string dataType)
        {
            string type = "TEXT";
            switch (dataType)
            {
                case ("System.Decimal"):
                case ("System.Double"):
                case ("System.Single"):
                    type = "DECIMAL";
                    break;

                case ("System.Boolean"):
                    type = "BIT";
                    break;

                case ("System.String"):
                    type = "NVARCHAR(MAX)";
                    break;

                case ("System.Int16"):
                case ("System.Int32"):
                case ("System.Int64"):
                case ("System.Byte"):
                    type = "INT";
                    break;

                case ("System.DateTime"):
                    type = "DATETIME";
                    break;
            }
            return type;
        }        

        private static bool ImportTableData(string connString, DataTable dataSource)
        {            
            //Open bulkcopy connection.
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkcopy.BulkCopyTimeout = 0;   // It doesn't matter how long as long as we get the results
                //Set destination table name to table previously created.
                bulkcopy.DestinationTableName = String.Format("[dbo].[{0}]", dataSource.TableName);
                try
                {
                    bulkcopy.WriteToServer(dataSource);
                }
                catch (Exception ex)
                {
                    string message = String.Format("Error occurred while loading data to the table {0}" +
                        "{1}{1}Error details:{1}{2}", dataSource.TableName, Environment.NewLine, ex.Message);
                    MessageBox.Show(message, AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                bulkcopy.Close();
            }
            return true;
        }

        private void DownloadingData_Shown(object sender, EventArgs e)
        {
            if (skipDownload)
            {
                btnCancel.Enabled = false;
                if (!downloadTimer.Enabled)
                {
                    startTime = DateTime.Now;
                    downloadTimer.Start();
                }
                backgroundWorker.RunWorkerAsync();
            }
            else
            {
                Uri uri = new Uri(downloadUrl);
                string[] segments = uri.Segments;
                string filename = segments[segments.Length - 1];

                string cacheFile = Properties.Settings.Default.CacheFileName;
                FileInfo cacheFileInfo = new FileInfo(cacheFile);

                destinationFile = String.Format(@"{0}\{1}", cacheFileInfo.DirectoryName, filename);
                webClient.DownloadFileAsync(uri, destinationFile);
                startTime = DateTime.Now;
                downloadTimer.Start();
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            DialogResult response = MessageBox.Show(this,
                "Are you sure you want to cancel this downloading?",
                "Downloading Offline data", 
                MessageBoxButtons.YesNo, MessageBoxIcon.Question);

            if (response == System.Windows.Forms.DialogResult.Yes) webClient.CancelAsync();
        }

        private void DownloadingData_Load(object sender, EventArgs e)
        {
            if (skipDownload) return;
            else
            {
                IQService webService = new IQService();
                webService.Url = AppConfiguration.WebServiceURL;

                try
                {
                    string url = webService.GetOfflineDataFileAfterCutOffPoint(cutOffPoint);
                    string message = String.Empty;
                    switch (url)
                    {
                        case Utilities.CACHEDB_STATUS_CREATING:
                            message = "The most recent offline data is being created at the moment.\n\n" +
                                "The process of creating offline data in the server may a long time depending " +
                                "on the amount of data available in the server.\n\n" +
                                "Please try again later after several minutes";
                            MessageBox.Show(this, message, AppConfiguration.ApplicationName,
                                MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                            DialogResult = System.Windows.Forms.DialogResult.Cancel;
                            Close();
                            break;

                        case Utilities.CACHEDB_STATUS_ERROR:
                            message = "There was an error while trying to retrieve offline data. " +
                                "Please contact your system administrator for further assistance";
                            MessageBox.Show(this, message, AppConfiguration.ApplicationName,
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
                            DialogResult = System.Windows.Forms.DialogResult.Cancel;
                            Close();
                            break;

                        case Utilities.CACHEDB_STATUS_READY:
                            message = "The system is ready but offline data could not be found in the server. " +
                                "Please contact your system administrator for further assistance";
                            MessageBox.Show(this, message, AppConfiguration.ApplicationName,
                                MessageBoxButtons.OK, MessageBoxIcon.Stop);
                            DialogResult = System.Windows.Forms.DialogResult.Cancel;
                            Close();
                            break;

                        default:
                            downloadUrl = url;
                            break;
                    }
                }
                catch (Exception  ex)
                {
                    MessageBox.Show(this, ex.Message, AppConfiguration.ApplicationName,
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
                    DialogResult = System.Windows.Forms.DialogResult.Cancel;
                    Close();
                }
            }
        }

        private void downloadTimer_Tick(object sender, EventArgs e)
        {
            TimeSpan timeSpan = DateTime.Now - startTime;
            lblTimeElapsed.Text = String.Format("Time elapsed: {0:00}:{1:00}:{2:00}",
                timeSpan.Hours, timeSpan.Minutes, timeSpan.Seconds);
            downloadTimer.Start();
        }

        private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            string cacheFile = Properties.Settings.Default.CacheFileName;
            //RestoringData(cacheFile);
            success = LoadingDBIntoSQLServer(cacheFile);
            if (success) File.Delete(cacheFile);
        }

        private void backgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            string text = e.UserState.ToString();
            lblStatus.Text = text;
            if (e.ProgressPercentage != -1) prbDownloading.Value = e.ProgressPercentage;
        }

        private void backgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            downloadTimer.Stop();
            if (success) this.DialogResult = System.Windows.Forms.DialogResult.OK;
            this.Close();
        }        

        //private void lblStatus_Paint(object sender, PaintEventArgs e)
        //{
        //    base.OnPaint(e);
        //    TextFormatFlags flags = TextFormatFlags.Bottom | TextFormatFlags.EndEllipsis;
        //    Rectangle labelSize = new Rectangle(lblStatus.Location, lblStatus.Size);
        //    TextRenderer.DrawText(e.Graphics, lblStatus.Text, this.Font, labelSize, SystemColors.ControlText, flags);
        //}

        public bool Completed
        {
            get
            {
                return this.success;
            }
        }

        #region Obselete Methods - Not used any more
        [Obsolete("Don't use this method -- waiting deletion", true)]
        private static bool CreateTable(string connString, string tableName, DataTable dataSource)
        {
            StringBuilder sBuilder = new StringBuilder();
            sBuilder.Append(String.Format("CREATE TABLE [{0}] (", tableName));
            string columnName = dataSource.Columns[0].ColumnName;
            string dataType = GetDBDataType(dataSource.Columns[0].DataType.ToString());
            sBuilder.Append(String.Format("[{0}] {1} NULL", columnName, dataType));

            for (int i = 1; i < dataSource.Columns.Count; i++)
            {
                sBuilder.Append(",");
                columnName = dataSource.Columns[i].ColumnName;
                dataType = GetDBDataType(dataSource.Columns[i].DataType.ToString());
                sBuilder.Append(String.Format("[{0}] {1} NULL", columnName, dataType));
            }
            sBuilder.Append(")");
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand command = new SqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.CommandText = sBuilder.ToString();
            command.ExecuteNonQuery();
            conn.Close();
            conn.Dispose();

            //Open bulkcopy connection.
            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkcopy.BulkCopyTimeout = 0;
                //Set destination table name to table previously created.
                bulkcopy.DestinationTableName = String.Format("[dbo].[{0}]", tableName);
                try
                {
                    bulkcopy.WriteToServer(dataSource);
                }
                catch (Exception ex)
                {
                    string message = String.Format("Error occurred while loading data to the table {0}" +
                        "{1}{1}Error details:{1}{2}", tableName, Environment.NewLine, ex.Message);
                    MessageBox.Show(message, AppConfiguration.ApplicationName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                bulkcopy.Close();
            }
            return true;
        }

        [Obsolete("Don't use this method -- waiting deletion", true)]
        private void RestoringData(string cacheFile)
        {
            DbCommand command = GenericDataAccess.CreateCommand(
                AppConfiguration.DBProviderName, AppConfiguration.SQLCacheDBConnection);
            string reportingMessage = null;
            DbParameter param = null;
            try
            {
                #region Importing data using SQL Server bulk insert
                SqlConnection.ClearAllPools();  // Tries to clear existing connections.
                command = GenericDataAccess.CreateCommand(
                    AppConfiguration.DBProviderName, AppConfiguration.SQLCacheDBConnection);
                command.Connection.Open();

                #region Creating Cache Database
                command.CommandText = "SELECT count(*) FROM master.dbo.sysdatabases WHERE name = @DatabaseName";
                param = command.CreateParameter();
                param.ParameterName = "@DatabaseName";
                param.DbType = DbType.String;
                param.Value = AppConfiguration.CacheDBName;
                command.Parameters.Add(param);
                string output = GenericDataAccess.ExecuteScalar(command, false);
                int count = Int32.Parse(output);
                if (count != 1)
                {
                    command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
                    reportingMessage = "Creating new cache database. Please wait...";
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                    command.ExecuteNonQuery();
                }
                else
                {
                    reportingMessage = "Cache database already existing. Preparing to recreate tables. Please wait...";
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                }

                /*try
                {
                    command.CommandText = String.Format("DROP DATABASE {0}", AppConfiguration.CacheDBName);
                    reportingMessage = "Deleting existing cache database. Please wait...";
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    reportingMessage = String.Format("Failed to delete database: {0}", ex.Message);
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                };
                try
                {
                    command.CommandText = String.Format("CREATE DATABASE {0}", AppConfiguration.CacheDBName);
                    reportingMessage = "Creating new cache database. Please wait...";
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    reportingMessage = String.Format("Failed to create database: {0}", ex.Message);
                    backgroundWorker.ReportProgress(-1, reportingMessage);
                };*/
                #endregion

                // Retrieving a list of table together with their data and insert into the database
                string cacheDBConnection = AppConfiguration.SQLCacheDBConnection.Replace("master", AppConfiguration.CacheDBName);
                command = GenericDataAccess.CreateCommand(AppConfiguration.DBProviderName, cacheDBConnection);
                command.CommandType = CommandType.Text;

                DataSet offlineDataSet = new DataSet();
                reportingMessage = "Reading data source. Please wait...";
                backgroundWorker.ReportProgress(-1, reportingMessage);
                offlineDataSet.ReadXml(cacheFile, XmlReadMode.ReadSchema);

                int totalProgress = offlineDataSet.Tables.Count;
                count = 0;
                int progress;
                foreach (DataTable dataTable in offlineDataSet.Tables)
                {
                    count++;
                    decimal percentage = 100 * (decimal)count / totalProgress;
                    progress = (int)percentage;
                    string tableName = dataTable.TableName;
                    try
                    {
                        reportingMessage = String.Format("Deleting old data table: {0}", tableName);
                        backgroundWorker.ReportProgress(progress, reportingMessage);

                        string deleteQuery = String.Format("DROP TABLE [{0}]", tableName);
                        command.CommandText = deleteQuery;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }
                    catch (Exception ex)
                    {
                        reportingMessage = String.Format("Failed to deleting old data table {0}: {1}", tableName, ex.Message);
                        backgroundWorker.ReportProgress(progress, reportingMessage);
                    }

                    reportingMessage = String.Format("Creating and inserting data into new table: {0}. Please wait...", tableName);
                    backgroundWorker.ReportProgress(progress, reportingMessage);
                    //CreateTable(cacheDBConnection, tableName, dataTable);
                }
                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                #endregion

                reportingMessage = "Finalizing importing data. Please wait...";
                backgroundWorker.ReportProgress(-1, reportingMessage);
                #region Encrypting users' passwords and updating CacheDB labels
                command.CommandText = @"SELECT ID, Password FROM Users";
                DataTable usersDataTable = GenericDataAccess.ExecuteSelectCommand(command, false);
                foreach (DataRow row in usersDataTable.Rows)
                {
                    string id = row["ID"].ToString();
                    string password = row["Password"].ToString();
                    string encryptedPassword = DataTransformation.Encrypt(password);
                    command.CommandText = @"UPDATE Users SET [Password]=@Password WHERE ID=@ID";
                    command.Parameters.Clear();

                    param = command.CreateParameter();
                    param.ParameterName = "@Password";
                    param.DbType = DbType.String;
                    param.Value = encryptedPassword;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@ID";
                    param.DbType = DbType.Int32;
                    param.Value = id;
                    command.Parameters.Add(param);
                    command.ExecuteNonQuery();
                }

                command.CommandText = @"INSERT INTO SysSettings(SettingName,SettingValue) 
                    VALUES(@SettingName, @SettingValue)";
                // The date of last update
                param = command.CreateParameter();
                param.ParameterName = "@SettingName";
                param.DbType = DbType.String;
                param.Value = Utilities.SETTING_CACHEDB_CREATE_DATE;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@SettingValue";
                param.DbType = DbType.String;
                param.Value = DateTime.Now.ToString("s");
                command.Parameters.Add(param);
                command.ExecuteNonQuery();

                // Data cut-off point
                command.Parameters.Clear();
                param = command.CreateParameter();
                param.ParameterName = "@SettingName";
                param.DbType = DbType.String;
                param.Value = Utilities.SETTING_CACHEDB_CUT_OFF_POINT;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@SettingValue";
                param.DbType = DbType.String;
                param.Value = this.cutOffPoint.ToString("s");
                command.Parameters.Add(param);
                command.ExecuteNonQuery();

                if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                #endregion

                this.success = true;
            }
            catch (Exception ex)
            {
                string message = String.Format("{0}{1}{1}The application failed to import data.",
                    ex.Message, Environment.NewLine);
                Utilities.LogError(ex, command);
                MessageBox.Show(this, message, AppConfiguration.ApplicationName,
                    MessageBoxButtons.OK, MessageBoxIcon.Error);

                this.success = false;
            }
        }
        #endregion
    }
}
